package com.bookstore.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.bookstore.model.User;
import com.bookstore.utils.DBConnection;
import com.mysql.jdbc.Statement;

public class UserDAOImpl implements UserDAO {

	private static final String FIND_ALL_USER = "select * from d_user";
	private static final String INSERT = "insert into d_user(email,nickname,password,user_integral,is_email_verify,email_verify_code,last_login_time,last_login_ip) values (?,?,?,?,?,?,?,?)";
	private static final String FINDBYEMAIL = "select * from d_user where email=?";
	private static final String FINDBYID = "select * from d_user where id=?";
	private static final String UPDATE = "update d_user set email=?,nickname=?,password=?,user_integral=?,is_email_verify=?,email_verify_code=?,last_login_time=?,last_login_ip=? where id=?";

	@SuppressWarnings("finally")
	@Override
	public List<User> findAll() throws Exception {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		List<User> list = new ArrayList<>();
		try {
			connection = DBConnection.getConnection();
			statement = connection.prepareStatement(FIND_ALL_USER);
			resultSet = statement.executeQuery();
			User user = null;
			while (resultSet.next()) {
				user = new User();
				user.setId(resultSet.getInt("id"));
				user.setEmail(resultSet.getString("email"));
				user.setNickname(resultSet.getString("nickname"));
				user.setPassword(resultSet.getString("password"));
				user.setUserIntegral(resultSet.getInt("user_integral"));
				user.setEmailVerify(resultSet.getBoolean("is_email_verify"));
				user.setEmailVerifyCode(resultSet.getString("email_verify_code"));
				user.setLastLoginTime(resultSet.getLong("last_login_time"));
				user.setLastLoginIp(resultSet.getString("last_login_ip"));
				list.add(user);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(resultSet, statement, connection);
			return list;
		}
	}

	@Override
	public User findByEmail(String email) throws Exception {
		PreparedStatement statement = null;
		Connection connection = null;
		User user = null;
		ResultSet resultSet = null;
		try {
			connection = DBConnection.getConnection();
			statement = connection.prepareStatement(FINDBYEMAIL);
			statement.setString(1, email);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				user = new User();
				user.setId(resultSet.getInt("id"));
				user.setEmail(resultSet.getString("email"));
				user.setNickname(resultSet.getString("nickname"));
				user.setPassword(resultSet.getString("password"));
				user.setUserIntegral(resultSet.getInt("user_integral"));
				user.setEmailVerifyCode(resultSet.getString("email_verify_code"));
				user.setLastLoginTime(resultSet.getLong("last_login_time"));
				user.setLastLoginIp(resultSet.getString("last_login_ip"));
				String isEmailVerify = resultSet.getString("is_email_verify");
				if (isEmailVerify.equals("T")) {
					user.setEmailVerify(true);
				} else {
					user.setEmailVerify(false);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(statement, connection);
		}
		return user;
	}

	@Override
	public User findById(int id) throws Exception {
		PreparedStatement statement = null;
		Connection connection = null;
		User user = null;
		ResultSet resultSet = null;
		try {
			connection = DBConnection.getConnection();
			statement = connection.prepareStatement(FINDBYID);
			statement.setInt(1, id);
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				user = new User();
				user.setId(resultSet.getInt("id"));
				user.setEmail(resultSet.getString("email"));
				user.setNickname(resultSet.getString("nickname"));
				user.setPassword(resultSet.getString("password"));
				user.setUserIntegral(resultSet.getInt("user_integral"));
				user.setEmailVerifyCode(resultSet.getString("email_verify_code"));
				user.setLastLoginTime(resultSet.getLong("last_login_time"));
				user.setLastLoginIp(resultSet.getString("last_login_ip"));
				String isEmailVerify = resultSet.getString("is_email_verify");
				if (isEmailVerify.equals("T")) {
					user.setEmailVerify(true);
				} else {
					user.setEmailVerify(false);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(statement, connection);
		}
		return user;
	}

	@Override
	public void update(User user) throws Exception {
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = DBConnection.getConnection();
			statement = connection.prepareStatement(UPDATE);
			statement.setString(1, user.getEmail());
			statement.setString(2, user.getNickname());
			statement.setString(3, user.getPassword());
			statement.setInt(4, user.getUserIntegral());
			if (user.isEmailVerify()) {
				statement.setString(5, "T");
			} else {
				statement.setString(5, "F");
			}
			statement.setString(6, user.getEmailVerifyCode());
			statement.setLong(7, user.getLastLoginTime());
			statement.setString(8, user.getLastLoginIp());
			statement.setInt(9, user.getId());
			
			statement.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(statement, connection);
		}
	}

	@Override
	public void save(User user) throws Exception {
		PreparedStatement statement = null;
		Connection connection = null;
		try {
			connection = DBConnection.getConnection();
			statement = connection.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, user.getEmail());
			statement.setString(2, user.getNickname());
			statement.setString(3, user.getPassword());
			statement.setInt(4, user.getUserIntegral());
			if (user.isEmailVerify()) {
				statement.setString(5, "T");
			} else {
				statement.setString(5, "F");
			}
			statement.setString(6, user.getEmailVerifyCode());
			statement.setLong(7, user.getLastLoginTime());
			statement.setString(8, user.getLastLoginIp());
			
			statement.executeUpdate();
			ResultSet resultSet = statement.getGeneratedKeys();
			resultSet.next();
			int id = resultSet.getInt(1);
			user.setId(id);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(statement, connection);
		}
	}

}
